package com.ocm.tourism.core.jdbc;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.ParsedSql;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Service;

import com.ocm.tourism.core.domain.Business;
import com.ocm.tourism.core.domain.BusinessType;
import com.ocm.tourism.core.domain.CulinaryCulture;
import com.ocm.tourism.core.domain.FoodStall;
import com.ocm.tourism.core.domain.Hotel;
import com.ocm.tourism.core.domain.PeriodTimeType;
import com.ocm.tourism.core.domain.Priority;
import com.ocm.tourism.core.domain.ScheduleTour;
import com.ocm.tourism.core.domain.TourType;
import com.ocm.tourism.core.jdbc.rowmapper.BusinessViewestRowMapper;
import com.ocm.tourism.core.jdbc.rowmapper.CommonCulinaryRowMapper;
import com.ocm.tourism.core.jdbc.rowmapper.FoodStallRowMapper;
import com.ocm.tourism.core.jdbc.rowmapper.HotelRowMapper;
import com.ocm.tourism.core.jdbc.rowmapper.TourAgencyAdvRowMapper;
import com.ocm.tourism.core.jdbc.rowmapper.TourAgencyRowMapper;

@Service("jdbcAvertisementDao")
public class JdbcAvertisementDao extends JdbcGenericDao implements AdvertisementDb {

   public static final int DEFAULT_CACHE_LIMIT = 256;


   private volatile int cacheLimit = DEFAULT_CACHE_LIMIT;

   private final Map<String, ParsedSql> parsedSqlCache =
         new LinkedHashMap<String, ParsedSql> (DEFAULT_CACHE_LIMIT, 0.75f, true) {
            private static final long serialVersionUID = 1L;

            @Override
            protected boolean removeEldestEntry(Map.Entry<String, ParsedSql> eldest) {
               return size() > getCacheLimit();
            }
         };

   @Autowired
   private SQLCommand sqlCommand;

   @Autowired
   public JdbcAvertisementDao(final DataSource dataSource) {
      super.setDataSource(dataSource);
   }


   public void setCacheLimit(int cacheLimit) {
      this.cacheLimit = cacheLimit;
   }

   public int getCacheLimit() {
      return this.cacheLimit;
   }
   
   protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
      ParsedSql parsedSql = getParsedSql(sql);
      String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
      Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
      int[] paramTypes = NamedParameterUtils.buildSqlTypeArray(parsedSql, paramSource);
      PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, paramTypes);
      return pscf.newPreparedStatementCreator(params);
   }

   protected ParsedSql getParsedSql(String sql) {
      if (getCacheLimit() <= 0) {
         return NamedParameterUtils.parseSqlStatement(sql);
      }
      synchronized (this.parsedSqlCache) {
         ParsedSql parsedSql = this.parsedSqlCache.get(sql);
         if (parsedSql == null) {
            parsedSql = NamedParameterUtils.parseSqlStatement(sql);
            this.parsedSqlCache.put(sql, parsedSql);
         }
         return parsedSql;
      }
   }

   @Override
   public List<Hotel> getSaleOffHotels(int firstResult, int maxResults) {
      String sql = sqlCommand.getSaleOffHotels();
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      paramSource.addValue("from", firstResult + 1);
      paramSource.addValue("to", firstResult + 1 + maxResults);
      PrepareStatementCallbackImpl<List<Hotel>> statementCallback = new PrepareStatementCallbackImpl<List<Hotel>>();
      statementCallback.setMaxRows(maxResults);
      statementCallback.setSql(sql);
      statementCallback.setRowMapper(new HotelRowMapper());
      List<Hotel> hotels = this.getJdbcTemplate().execute(getPreparedStatementCreator(sql, paramSource),statementCallback);
      return hotels;
   }

   @Override
   public long countSaleOffHotels() {
      String sql = sqlCommand.countHotelAdvs();
      Long count = this.getJdbcTemplate().queryForLong(sql);
      return count;
   }

   @Override
   public List<CulinaryCulture> getRestaurantAdvs(int firstResult, int maxResults) {
      String sql = sqlCommand.getRestaurantAdvs();
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      paramSource.addValue("from", firstResult + 1);
      paramSource.addValue("to", firstResult + 1 + maxResults);
      PrepareStatementCallbackImpl<List<CulinaryCulture>> statementCallback = new PrepareStatementCallbackImpl<List<CulinaryCulture>>();
      statementCallback.setSql(sql);
      statementCallback.setNativeJdbcExtractor(this.getJdbcTemplate().getNativeJdbcExtractor());
      statementCallback.setMaxRows(maxResults);
      statementCallback.setRowMapper(new CommonCulinaryRowMapper());
      List<CulinaryCulture> culinaryCultures = this.getJdbcTemplate().execute(getPreparedStatementCreator(sql, paramSource),statementCallback);
      return culinaryCultures;
   }

   @Override
   public List<CulinaryCulture> getCoffeeAdvs(int firstResult, int maxResults) {
      String sql = sqlCommand.getCoffeeAdvs();
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      paramSource.addValue("from", firstResult + 1);
      paramSource.addValue("to", firstResult + 1 + maxResults);
      PrepareStatementCallbackImpl<List<CulinaryCulture>> statementCallback = new PrepareStatementCallbackImpl<List<CulinaryCulture>>();
      statementCallback.setSql(sql);
      statementCallback.setNativeJdbcExtractor(this.getJdbcTemplate().getNativeJdbcExtractor());
      statementCallback.setMaxRows(maxResults);
      statementCallback.setRowMapper(new CommonCulinaryRowMapper());
      List<CulinaryCulture> culinaryCultures = this.getJdbcTemplate().execute(getPreparedStatementCreator(sql, paramSource),statementCallback);
      return culinaryCultures;
   }


   @Override
   public List<FoodStall> getFoodStallAdvs(int firstResult, int maxResults) {
      String sql = sqlCommand.getFoodStallAdvs();
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      paramSource.addValue("from", firstResult + 1);
      paramSource.addValue("to", firstResult + 1 + maxResults);
      PrepareStatementCallbackImpl<List<FoodStall>> statementCallback = new PrepareStatementCallbackImpl<List<FoodStall>>();
      statementCallback.setSql(sql);
      statementCallback.setNativeJdbcExtractor(this.getJdbcTemplate().getNativeJdbcExtractor());
      statementCallback.setMaxRows(maxResults);
      statementCallback.setRowMapper(new FoodStallRowMapper());
      List<FoodStall> foodStalls = this.getJdbcTemplate().execute(getPreparedStatementCreator(sql, paramSource),statementCallback);
      return foodStalls;
   }


   @Override
   public List<FoodStall> getFoodStallAdvs(int firstResult, int maxResults, int periodTime) {
      String sql = sqlCommand.getFoodStallAdvsByPeriodTime();
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      paramSource.addValue("periodtimes", Arrays.asList(PeriodTimeType.getType(periodTime).getPeriodTimeNumbers()));
      paramSource.addValue("from", firstResult + 1);
      paramSource.addValue("to", firstResult + 1 + maxResults);
      PrepareStatementCallbackImpl<List<FoodStall>> statementCallback = new PrepareStatementCallbackImpl<List<FoodStall>>();
      statementCallback.setSql(sql);
      statementCallback.setNativeJdbcExtractor(this.getJdbcTemplate().getNativeJdbcExtractor());
      statementCallback.setMaxRows(maxResults);
      statementCallback.setRowMapper(new FoodStallRowMapper());
      List<FoodStall> foodStalls = this.getJdbcTemplate().execute(getPreparedStatementCreator(sql, paramSource),statementCallback);
      return foodStalls;
   }


   @Override
   public long countFoodStallAdvs(int periodtime) {
      Integer[] periodtimes = PeriodTimeType.getType(periodtime).getPeriodTimeNumbers();
      String expersion = "";
      if (periodtimes.length > 0){
         int index=0;
         expersion = expersion + periodtimes[index];
         while ((++index) < periodtimes.length){
            expersion += "," + periodtimes[index];
         }
      }
      String sql = "SELECT count(bus_id) as count from \"LISTFOODSTALLS\" WHERE bus_periodtime IN ("+expersion+")";
      Long count = this.getJdbcTemplate().queryForLong(sql) ;
      return count;
   }


   @Override
   public long countFoodStallAdvs() {
      String sql = sqlCommand.countFoodStallAdvs();
      Long count = this.getJdbcTemplate().queryForLong(sql);
      return count;
   }


   @Override
   public long countTayNinhTourAdvs() {
      long count = countTayNinhTourAdvs(Priority.TOP1, Priority.TOP2);
      return count;
   }

   @Override
   public long countOtherTayNinhTourAdvs() {
      long count = countTayNinhTourAdvs(Priority.TOP3, Priority.TOP4);
      return count;
   }
   
   public long countTayNinhTourAdvs(Priority... priorities) {
      String expersion = "";
      if (priorities.length > 0){
         int index=0;
         expersion = expersion + "'" + priorities[index].name() + "'";
         while ((++index) < priorities.length){
            expersion += ","+ "'" + priorities[index].name() + "'";
         }
      }
      String sql = "SELECT count(bus_id) as count from \"LISTTOURISTAGENCYADVS\" WHERE sad_tour_type='"+TourType.TAYNINH.name()+"' and sad_ad_priority IN ("+expersion+")";
      Long count = this.getJdbcTemplate().queryForLong(sql) ;
      return count;
   }


   @Override
   public List<ScheduleTour> getTayNinhTourAdvs(int firstResult, int maxResults) {
      String sql = sqlCommand.getTourAgencyAdvs();
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      paramSource.addValue("tourtype", TourType.TAYNINH.name());
      List<String> priorities = new ArrayList<String>();
      priorities.add(Priority.TOP1.name());
      priorities.add(Priority.TOP2.name());
      paramSource.addValue("priorities", priorities);
      paramSource.addValue("from", firstResult + 1);
      paramSource.addValue("to", firstResult + 1 + maxResults);
      PrepareStatementCallbackImpl<List<ScheduleTour>> statementCallback = new PrepareStatementCallbackImpl<List<ScheduleTour>>();
      statementCallback.setSql(sql);
      statementCallback.setNativeJdbcExtractor(this.getJdbcTemplate().getNativeJdbcExtractor());
      statementCallback.setMaxRows(maxResults);
      statementCallback.setRowMapper(new TourAgencyAdvRowMapper());
      List<ScheduleTour> scheduleTours = this.getJdbcTemplate().execute(getPreparedStatementCreator(sql, paramSource),statementCallback);
      return scheduleTours;
   }


   @Override
   public List<ScheduleTour> getOtherTayNinhTourAdvs(int firstResult, int maxResults) {
      String sql = sqlCommand.getTourAgencyAdvs();
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      paramSource.addValue("tourtype", TourType.TAYNINH.name());
      List<String> priorities = new ArrayList<String>();
      priorities.add(Priority.TOP3.name());
      priorities.add(Priority.TOP4.name());
      paramSource.addValue("priorities", priorities);
      paramSource.addValue("from", firstResult + 1);
      paramSource.addValue("to", firstResult + 1 + maxResults);
      PrepareStatementCallbackImpl<List<ScheduleTour>> statementCallback = new PrepareStatementCallbackImpl<List<ScheduleTour>>();
      statementCallback.setSql(sql);
      statementCallback.setNativeJdbcExtractor(this.getJdbcTemplate().getNativeJdbcExtractor());
      statementCallback.setMaxRows(maxResults);
      statementCallback.setRowMapper(new TourAgencyAdvRowMapper());
      List<ScheduleTour> scheduleTours = this.getJdbcTemplate().execute(getPreparedStatementCreator(sql, paramSource),statementCallback);
      return scheduleTours;
   }


   @Override
   public long countToursByTourType(TourType tourType) {
      String sql = "SELECT count(bus_id) from \"LISTTOURISTAGENCIES\" WHERE sad_tour_type ='"+tourType.name()+"'";
      Long count = this.getJdbcTemplate().queryForLong(sql);
      return count;
   }


   @Override
   public List<ScheduleTour> getToursByTourType(TourType tourType, int firstResult, int maxResults) {
      String sql = sqlCommand.getTourAgencies();
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      paramSource.addValue("tourtype", tourType.name());
      paramSource.addValue("from", firstResult + 1);
      paramSource.addValue("to", firstResult + 1 + maxResults);
      PrepareStatementCallbackImpl<List<ScheduleTour>> statementCallback = new PrepareStatementCallbackImpl<List<ScheduleTour>>();
      statementCallback.setSql(sql);
      statementCallback.setNativeJdbcExtractor(this.getJdbcTemplate().getNativeJdbcExtractor());
      statementCallback.setMaxRows(maxResults);
      statementCallback.setRowMapper(new TourAgencyRowMapper());
      List<ScheduleTour> scheduleTours = this.getJdbcTemplate().execute(getPreparedStatementCreator(sql, paramSource),statementCallback);
      return scheduleTours;
   }


   @Override
   public List<Business> getBusinessViewest(int firstResult, int maxResults, BusinessType... businessTypes) {
      String sql = sqlCommand.getBusinessesViewestByBusinessTypes();
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      List<String> businessTypeNames = new ArrayList<String>(); 
      for (BusinessType businessType : businessTypes){
         businessTypeNames.add(businessType.name());
      }
      paramSource.addValue("businessTypes", businessTypeNames);
      PrepareStatementCallbackImpl<List<Business>> statementCallback = new PrepareStatementCallbackImpl<List<Business>>();
      statementCallback.setSql(sql);
      statementCallback.setNativeJdbcExtractor(this.getJdbcTemplate().getNativeJdbcExtractor());
      statementCallback.setMaxRows(maxResults);
      statementCallback.setRowMapper(new BusinessViewestRowMapper());
      List<Business> businesses = this.getJdbcTemplate().execute(getPreparedStatementCreator(sql, paramSource),statementCallback);
      return businesses;
   }


   @Override
   public long countBusinessViewest(BusinessType... businessTypes) {
      String expersion = "";
      if (businessTypes.length > 0){
         int index=0;
         expersion = expersion + "'" + businessTypes[index].name() + "'";
         while ((++index) < businessTypes.length){
            expersion += ","+ "'" + businessTypes[index].name() + "'";
         }
      }
      String sql = "SELECT count(bus_id) as count from \"LISTBUSINESSVIEWEST\" WHERE bus_type IN ("+expersion+")";
      Long count = this.getJdbcTemplate().queryForLong(sql) ;
      return count;
   }
}
